<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/8/9
 * Time: 11:22
 */

namespace app\index\controller;


use think\Request;
use think\Cache;
use think\Db;
use app\api\model\OrderModel;
use think\paginator\driver\Bootstrap;
use phpcvs\Csv;
use app\api\model\Approval;

class Statistics extends Backend
{
    /**
     * 订单列表
     */
    public function index()
    {
        $data     = Request::instance()->get();
        $admin    = $this->check_authority();
        $relation = db('relation')->where('user_id', $admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $model    = db('order')
            ->alias('a')->field('a.*,b.title,us.username as namekl,us.ce,st.store_name,cy.residential_id,cy.management,group.username as content1,goo.title as problemTitle,concat(p.province,c.city,u.county,a.addres) as  address,channel_details.title as channel_detailsTitle,c.city,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->join('chanel b', 'a.channel_id=b.id', 'left')
            ->join('channel_details', 'a.channel_details=channel_details.id', 'left')
            ->join('goods_category goo', 'a.pro_id=goo.id', 'left')
            
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('personal', 'a.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('store st', 'st.store_id=us.store_id', 'left')
            ->join('community cy', 'a.order_id=cy.id', 'left')
            ->join('group_community group', 'order.group_community=group.group_id', 'left');
        if (isset($data['status']) && $data['status'] != '') {
            
            if ($data['status'] == 20) {
                $model->where(['a.state' => 0]);
            } elseif ($data['status'] == 6) {
                $model->where(['a.state' => 7, 'a.product_id' => ['<>', 0]]);
            } elseif ($data['status'] == 7) {
                $model->where(['a.state' => $data['status'], 'a.product_id' => ['EQ', 0]]);
            } elseif ($data['status'] == 5) {
                $model->where(['a.state' => ['between', [4, 5]]])->order('a.order_id desc');
            } else {
                $model->where(['a.state' => $data['status']])->order('a.order_id desc');
            }
            if ($data['status'] != 0 && $data['status'] != 20) {
                //                $model->where(['us.ce'=>['<>', 2]]);
            }
        }
        if (!empty($data['addres'])) {
            $model->where(['a.addres' => ['like', "%{$data['addres']}%"]]);
            
        }
        
        if (!empty($data['city_id'])) {
            
            $model->where(['a.city_id' => $data['city_id']]);
        }
        if (isset($data['management']) && $data['management'] != '') {
            
            $model->where(['cy.management|personal.username' => ['like', "%{$data['management']}%"]]);
        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $model->where(['group.username|group_community.username' => ['like', "%{$data['residential_id']}%"]]);
            
        }
        if ($node['rule'] != '*') {
            $kl = 2;
            if (!empty($this->admin['store_id'])) {
                $model->where(['st.store_id' => $this->admin['store_id']]);
            }
            
            if (!empty($this->admin['qu'])) {
                $model->whereIn('a.channel_id', $this->admin['qu']);
            }
            
            
            if (!empty($this->admin['residential_id'])) {
                $model->where(function ($query) {
                    $query->whereIn('order.group_community', $this->admin['residential_id'])->whereOr(['group_community.group_id' => ['in', $this->admin['residential_id']]]);
                });
            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')){
                    
                    $model->where('order.group_community','0000001');
                    
                    
                    
                }
                
            }
            
            
        } else {
            $kl = 2;
        }
        if (isset($data['start_time']) && $data['start_time'] != '') {
            
            $model->where(['a.created_time' => ['>=', strtotime($data['start_time'])]]);
        }
        if (isset($data['end_time']) && $data['end_time'] != '') {
            
            $model->where(['a.created_time' => ['<=', strtotime($data['end_time']) + 24 * 3600]]);
        }
        
        if (isset($data['contacts']) && $data['contacts'] != '' && $data['chen'] != '' && $data['chen']) {
            
            $model->where(['a.' . $data['chen'] . '' => ['like', "%{$data['contacts']}%"]]);
        };
        if (isset($data['channel_id']) && $data['channel_id'] != '') {
            
            $model->where(['a.channel_id' => $data['channel_id']]);
            
        };
        if (isset($data['full']) && $data['full'] != '') {
            $product = db('product')->where(['full' => ['like', "%{$data['full']}%"]])->distinct(true)->column('orders_id');
            
            $model->whereIn('a.order_id', $product);
            
        };
        if (isset($data['store_id']) && $data['store_id'] != '') {
            
            $model->where(['us.store_id' => $data['store_id']]);
            
            
        };
        if (isset($data['assignor']) && $data['assignor'] != '') {
            $model->where(['us.username' => ['like', "%{$data['assignor']}%"]]);
        };
        
        if (isset($data['remote_time']) && $data['remote_time'] != '') {
            $remote_time = $data['remote_time'];
            
            $ytime  = strtotime(date("Y-m-d", strtotime($data['remote_time'])));//昨天开始时间戳
            $yetime = $ytime + 24 * 60 * 60 - 1;//昨天结束时间戳
            $title  = db('through')->where(['end_time' => ['between', [$ytime, $yetime]]])->field('order_ids')->select();
            $s      = '';
            foreach ($title as $key => $val) {
                $s .= $val['order_ids'] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');
            if ($data) {
                $model->where(['a.order_id' => ['in', $data]]);
            }
            
        };
        if (isset($data['remote']) && $data['remote'] != '') {
            $title = db('through')->where(['status' => ['like', "%{$data['remote']}%"]])->field('order_ids')->order('through_id desc')->select();
            $s     = '';
            foreach ($title as $key => $val) {
                $s .= $val["order_ids"] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');
            
            if ($data) {
                $model->where(['a.order_id' => ['in', $data]]);
            }
            
        };
//
//                $list=$model->order('a.order_id desc')->select();
//        //
//        //
//                echo $model->getLastsql();
//                die;
        $list = $model->order('a.order_id desc')->paginate(30)->each(function ($item, $key) {
            if ($item['state'] == 7 && $item['product_id'] == 0) {
                $item['states'] = "已完工";
            } elseif ($item['state'] == 7 && $item['product_id'] != 0) {
                $item['states'] = "已完成";
            } else {
                $item['states'] = OrderModel::type($item['state']);
            }
            $th_time = db('through')->where(['order_ids' => $item['order_id'], 'role' => 1])->order('through_id desc')->field('th_time,end_time')->find();
            if ($item['channel_id'] == 35 && ($item['channel_details'] == 121 || $item['channel_details'] == 120|| $item['channel_details'] == 119) && $item['tui_jian'] != 0) {
                $item['management']           = $item['username2'];
                $item['content1']             = empty($item['usernames']) ? $item['content1'] : $item['usernames'];
                $item['residential_quarters'] = $item['usernames1'];
            }
            $item['th_time']  = $th_time['th_time'];
            $item['end_time'] = $th_time['end_time'];
            $subhead=db('order_problem')->where(['order_id' => $item['order_id']])->where('goods_category_id',$item['pro_id'])->column('subhead');
            
            $item['problemTitle1'] = implode($subhead,',');
            return $item;
            
        });
        
        if ($admin['qu']) {
            $p = db('chanel')->where('type', 1)->whereIn('id', $admin['qu'])->select();
            
        } else {
            $p = db('chanel')->where('type', 1)->select();
        }
        
        if ($node['rule'] != '*') {
            $l = db('store');
            if (!empty($this->admin['store_id'])) {
                $l->where(['store_id' => $this->admin['store_id']]);
            }
            $l = $l->select();
        } else {
            $l = db('store')->select();
        }
        
        $r    = db('remote')->where(['is_enable' => 1, 'parent_id' => 0])->select();
        $city = db('city')->where("city_id", "in", "241,239")->select();
        if ($node['rule'] != '*') {
            $residential = db('group_community')->whereIn('group_id', $this->admin['residential_id'])->select();
        } else {
            $residential = db('group_community')->where('pid',0)->select();
        }
        return $this->fetch('index', ['list' => $list, 'p' => $p, 'l' => $l, 'r' => $r, 'city' => $city, 'node' => $node, 'admin' => $admin, 'remote_time' => !empty($remote_time) ? $remote_time : '', 'hide' => $admin['hide'], 'kl' => $kl, 'data' => $data, 'ls' => $residential]);
    }
    
    /**
     * 订单转化数据统计
     */
    public function zhuan()
    {
        $data = Request::instance()->get();
        
        $model = db('zhuan')->field('zhuan.*,st.store_name')->join('store st', 'st.store_id=zhuan.store_id', 'left');
        if (isset($data['store_id']) && $data['store_id'] != '') {
            
            $model->where(['zhuan.store_id' => $data['store_id']]);
        };
        
        if (isset($data['start_time']) && $data['start_time'] != '') {
            $start = strtotime($data['start_time'] . '-01 00:00:00');
            $end   = strtotime($data['start_time'] . '-' . date('t', strtotime($data['start_time'])) . ' 23:59:59');
            
            $model->where(['zhuan.date' => ['between', [$start, $end]]]);
        } else {
            $data['start_times'] = date('Y-m', time());
            $start               = strtotime($data['start_times'] . '-01 00:00:00');
            $end                 = strtotime($data['start_times'] . '-' . date('t', strtotime($data['start_times'])) . ' 23:59:59');
            $model->where(['zhuan.date' => ['between', [$start, $end]]]);
            
        };
        
        $list = $model->order('zhuan.date desc')->paginate(40);
        
        $l      = db('store')->select();
        $wheres = [];
        
        if (isset($data['start_time']) && $data['start_time'] != '') {
            $start          = strtotime($data['start_time'] . '-01 00:00:00');
            $end            = strtotime($data['start_time'] . '-' . date('t', strtotime($data['start_time'])) . ' 23:59:59');
            $wheres['date'] = ['between', [$start, $end]];
            
        } else {
            $data['start_times'] = date('Y-m', time());
            $start               = strtotime($data['start_times'] . '-01 00:00:00');
            $end                 = strtotime($data['start_times'] . '-' . date('t', strtotime($data['start_times'])) . ' 23:59:59');
            $wheres['date']      = ['between', [$start, $end]];
            
        };
        if (isset($data['store_id']) && $data['store_id'] != '') {
            
            $wheres['store_id'] = $data['store_id'];
            
        };
        
        $list1 = db('zhuan')->where($wheres)->sum('count');
        
        $list9 = db('zhuan')->where($wheres)->sum('you_count');
        $list2 = db('zhuan')->where($wheres)->sum('shang_count');
        $list3 = db('zhuan')->where($wheres)->sum('deal');
        $list4 = db('zhuan')->where($wheres)->sum('turnover');
        $list5 = db('zhuan')->where($wheres)->sum('unit');
        if (empty($list1)) {
            
            $list8 = 0;
        } else {
            $list8 = round(($list3 / $list1) * 100, 2);
        }
        if (empty($list9)) {
            $list6 = 0;
            
        } else {
            $list6 = round(($list2 / $list9) * 100, 2);
        }
        if (empty($list2)) {
            $list7 = 0;
        } else {
            $list7 = round(($list3 / $list2) * 100, 2);
        }
        return $this->fetch('zhuan', ['list' => $list, 'l' => $l, 'list1' => $list1, 'list2' => $list2, 'list3' => $list3, 'list4' => $list4, 'list5' => $list5, 'list6' => $list6, 'list7' => $list7, 'list8' => $list8, 'list9' => $list9]);
    }
    
    /**
     * 店铺／店长业绩数据
     */
    public function dian()
    {
        
        $data  = Request::instance()->get();
        $model = db('dian')->field('dian.*,us.username')->join('user us', 'us.user_id=dian.user_id', 'left');
        if (isset($data['start_time']) && $data['start_time'] != '') {
            $start = strtotime($data['start_time'] . '-01 00:00:00');
            $end   = strtotime($data['start_time'] . '-' . date('t', strtotime($data['start_time'])) . ' 23:59:59');
            $model->where(['dian.date' => ['between', [$start, $end]]]);
            
        } else {
            $data['start_times'] = date('Y-m', time());
            $start               = strtotime($data['start_times'] . '-01 00:00:00');
            $end                 = strtotime($data['start_times'] . '-' . date('t', strtotime($data['start_times'])) . ' 23:59:59');
            $model->where(['dian.date' => ['between', [$start, $end]]]);
            
        };;
        if (isset($data['user_id']) && $data['user_id'] != '') {
            
            $model->where('dian.user_id', $data['user_id']);
            
        };
        
        $list  = $model->order('dian.date desc')->paginate(40);
        $where = [];
        if (isset($data['start_time']) && $data['start_time'] != '') {
            $start         = strtotime($data['start_time'] . '-01 00:00:00');
            $end           = strtotime($data['start_time'] . '-' . date('t', strtotime($data['start_time'])) . ' 23:59:59');
            $where['date'] = ['between', [$start, $end]];
            
        } else {
            $data['start_times'] = date('Y-m', time());
            $start               = strtotime($data['start_times'] . '-01 00:00:00');
            $end                 = strtotime($data['start_times'] . '-' . date('t', strtotime($data['start_times'])) . ' 23:59:59');
            $where['date']       = ['between', [$start, $end]];
            
        };
        if (isset($data['user_id']) && $data['user_id'] != '') {
            
            $where['user_id'] = $data['user_id'];
            
        };
        $list1 = db('dian')->where($where)->sum('count');
        $list2 = db('dian')->where($where)->sum('shang_count');
        $list3 = db('dian')->where($where)->sum('deal');
        $list4 = db('dian')->where($where)->sum('turnover');
        $list5 = db('dian')->where($where)->sum('unit');
        if (empty($list1)) {
            $list6 = 0;
            $list8 = 0;
        } else {
            $list6 = round(($list2 / $list1) * 100, 2);
            $list8 = round(($list3 / $list1) * 100, 2);
        }
        if (empty($list2)) {
            $list7 = 0;
        } else {
            $list7 = round(($list3 / $list2) * 100, 2);
        }
        
        
        $l = db('user')->where('status', 0)->select();
        return $this->fetch('dian', ['list' => $list, 'l' => $l, 'list1' => $list1, 'list2' => $list2, 'list3' => $list3, 'list4' => $list4, 'list5' => $list5, 'list6' => $list6, 'list7' => $list7, 'list8' => $list8]);
    }
    
    /**
     * 渠道数据统计
     */
    public function qu()
    {
        $data = Request::instance()->get();
        $mp   = db('order')->join('city', 'order.city_id=city.city_id', 'left')->join('chanel', 'chanel.id=order.channel_id', 'left')->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,order.channel_id,city.city,order.created_time,chanel.title,order.city_id')->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),order.channel_id,order.city_id");
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start = strtotime($data['start_time'] . ' 00:00:00');
            $end   = strtotime($data['end_time'] . ' 23:59:59');
            Cache::set('qu.start_time', $data['start_time']);
            Cache::set('qu.end_time', $data['end_time']);
            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        if (!empty($data['channel_id']) && !empty($data['channel_id'])) {
            $mp->where('order.channel_id', intval($data['channel_id']));
        }
        
        if (isset($data['city']) && $data['city'] != '') {
            $mp->where('order.city_id', $data['city']);
        }
        $list = $mp->order('posttime desc')->paginate(15)->each(function ($item, $key) {
            $OrderQuantity = [];
            $deal          = [];
            $todayStart    = strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd      = strtotime($item['posttime'] . ' 23:59:59');
            //线索量
            $order = db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id' => $item['city_id'], 'channel_id' => $item['channel_id']])->field('city_id,channel_id,planned,state')->select();
            
            foreach ($order as $value) {
                //订单量
                if ($value['state'] != 10) {
                    $OrderQuantity[] = $value;
                } //上门量
                if (!empty($value['planned'])) {
                    $deal[] = $value;
                }
            }
            $item['order']                     = count($order);
            $item['OrderQuantity']             = count($OrderQuantity);
            $item['Efficiency']                = sprintf("%.2f", count($OrderQuantity) / count($order));
            $item['dealCount']                 = count($deal);
            $item['doorToDoorRate']            = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", count($deal) / count($OrderQuantity));
            $signUp                            = db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.channel_id' => $item['channel_id']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            $item['signUp']                    = count($signUp);
            $item['through']                   = db('through')->join('order', 'order.order_id=through.order_ids')->whereBetween('through.th_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.channel_id' => $item['channel_id']])->count();
            $item['remoteProportion']          = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", $item['through'] / count($OrderQuantity));
            $item['doorToDoorTransactionRate'] = empty($deal) ? 0 : sprintf("%.2f", $item['signUp'] / count($deal));
            $item['orderTurnoverRate']         = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", $item['signUp'] / count($OrderQuantity));
            if (!empty($signUp)) {
                $amount = 0;
                foreach ($signUp as $o) {
                    $turnover0 = (new OrderModel())->newOffer($o);
                    $amount    += $turnover0['amount'];
                }
            } else {
                $amount = 0;
            }
            $item['turnover']  = $amount;
            $item['turnoverS'] = empty($item['signUp']) ? 0 : sprintf("%.2f", $amount / $item['signUp']);
            return $item;
        });
        $p    = db('chanel')->where('type', 1)->select();
        return $this->fetch('qu', ['list' => $list, 'p' => $p]);
    }
    
    /**
     * 渠道SKU数据统计
     */
    public function sku()
    {
        $data = Request::instance()->get();
        $mp   = db('order')->join('city', 'order.city_id=city.city_id', 'left')->join('chanel', 'chanel.id=order.channel_id', 'left')->join('goods_category', 'goods_category.id=order.pro_id', 'left')->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,order.channel_id,order.pro_id,order.pro_id1,city.city,order.created_time,chanel.title,order.city_id,goods_category.title as title0')->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),order.channel_id,order.city_id,order.pro_id");
        
        if (!empty($data['channel_id']) && !empty($data['channel_id'])) {
            $mp->where('order.channel_id', intval($data['channel_id']));
        }
        if (!empty($data['pro_id']) && !empty($data['pro_id'])) {
            $mp->where('order.pro_id', intval($data['pro_id']));
        }
        if (isset($data['city']) && $data['city'] != '') {
            $mp->where('order.city_id', $data['city']);
        }
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start = strtotime($data['start_time'] . ' 00:00:00');
            $end   = strtotime($data['end_time'] . ' 23:59:59');
            Cache::set('sku.start_time', $data['start_time']);
            Cache::set('sku.end_time', $data['end_time']);
            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        $list           = $mp->order('posttime desc')->paginate(15)->each(function ($item, $key) {
            $OrderQuantity = [];
            $deal          = [];
            $todayStart    = strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd      = strtotime($item['posttime'] . ' 23:59:59');
            //线索量
            $order = db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id' => $item['city_id'], 'channel_id' => $item['channel_id'], 'pro_id' => $item['pro_id']])->field('city_id,channel_id,planned,state')->select();
            
            foreach ($order as $value) {
                //订单量
                if ($value['state'] != 10) {
                    $OrderQuantity[] = $value;
                } //上门量
                if (!empty($value['planned'])) {
                    $deal[] = $value;
                }
            }
            $item['order']                     = count($order);
            $item['OrderQuantity']             = count($OrderQuantity);
            $item['Efficiency']                = sprintf("%.2f", count($OrderQuantity) / count($order));
            $item['dealCount']                 = count($deal);
            $item['doorToDoorRate']            = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", count($deal) / count($OrderQuantity));
            $signUp                            = db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.channel_id' => $item['channel_id'], 'order.pro_id' => $item['pro_id']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            $item['signUp']                    = count($signUp);
            $item['doorToDoorTransactionRate'] = empty($deal) ? 0 : sprintf("%.2f", $item['signUp'] / count($deal));
            $item['orderTurnoverRate']         = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", $item['signUp'] / count($OrderQuantity));
            if (!empty($signUp)) {
                $amount = 0;
                foreach ($signUp as $o) {
                    $turnover0 = (new OrderModel())->newOffer($o);
                    $amount    += $turnover0['amount'];
                }
            } else {
                $amount = 0;
            }
            $item['turnover']  = $amount;
            $item['turnoverS'] = empty($item['signUp']) ? 0 : sprintf("%.2f", $amount / $item['signUp']);
            return $item;
        });
        $p              = db('chanel')->where('type', 1)->select();
        $goods_category = db('goods_category')->where('parent_id', 0)->select();
        $city           = db('city')->where("city_id", "in", "241,239")->select();
        return $this->fetch('sku', ['list' => $list, 'p' => $p, 'goods_category' => $goods_category]);
    }
    
    /**
     * 取消原因数据统计
     */
    public function xiao()
    {
        $data = Request::instance()->get();
        
        $p = db('order')->join('city', 'order.city_id=city.city_id', 'left')->join('chanel', 'chanel.id=order.channel_id', 'left')->join('community', 'community.id = order.order_id', 'left')->where('order.state', 8);
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start = strtotime($data['start_time'] . ' 00:00:00');
            $end   = strtotime($data['end_time'] . ' 23:59:59');
            Cache::set('xiao.start_time', $data['start_time']);
            Cache::set('xiao.end_time', $data['end_time']);
            $p->whereBetween('order.tui_time', [$start, $end]);
        }
        if (!empty($data['channel_id']) && !empty($data['channel_id'])) {
            $p->where('order.channel_id', intval($data['channel_id']));
        }
        
        if (isset($data['city']) && $data['city'] != '') {
            $p->where('order.city_id', $data['city']);
        }
        $list = $p->group('order.city_id,order.channel_id')->field('order.order_id,order.city_id,city.city,order.state,order.tui_time,chanel.title,count(*) as count,chanel.id')->limit(10)->order('order.tui_time desc')->paginate(20)->each(function ($item, $key) use ($data) {
            $item['th_time'] = date('Y-m-d', $item['tui_time']);
            $oreder          = db('order')->where('order.state', 8);
            if (isset($data['start_time']) && $data['start_time'] != '') {
                $starttime = date('Y-m-01', strtotime($data['start_time']));
                $enttime   = strtotime(date('Y-m-d', strtotime("$starttime +1 month")));
                $oreder->whereBetween('order.tui_time', [$starttime, $enttime]);
            }
            $order_id = $oreder->where(['channel_id' => $item['id'], 'city_id' => $item['city_id']])->field('order_id,reason')->select();
            
            $o = ['没有及时联系我', '没有给方案报价', '工期太长太麻烦', '超出业务范围', '价格比其他家高', '价格比客户心里预期高', '嫌麻烦', '工期太长不合适', '担心环保', '找熟人朋友', '不做了'];
            foreach ($order_id as $value) {
                if (stristr($value['reason'], $o[0])) {
                    $qux_count[] = $value;
                }
                if (stristr($value['reason'], $o[1])) {
                    $qux_count1[] = $value;
                }
                if (stristr($value['reason'], $o[2])) {
                    $qux_count2[] = $value;
                }
                if (stristr($value['reason'], $o[3])) {
                    $qux_count3[] = $value;
                }
                if (stristr($value['reason'], $o[4])) {
                    $qux_count4[] = $value;
                }
                if (stristr($value['reason'], $o[5])) {
                    $qux_count5[] = $value;
                }
                if (stristr($value['reason'], $o[6])) {
                    $qux_count6[] = $value;
                }
                if (stristr($value['reason'], $o[7])) {
                    $qux_count7[] = $value;
                }
                if (stristr($value['reason'], $o[7])) {
                    $qux_count8[] = $value;
                }
                if (stristr($value['reason'], $o[9])) {
                    $qux_count9[] = $value;
                }
                if (stristr($value['reason'], $o[10])) {
                    $qux_count10[] = $value;
                }
            }
            $item['qux_count']       = !empty($qux_count) ? count($qux_count) : 0; //取消占比
            $item['qux_count1']      = !empty($qux_count1) ? count($qux_count1) : 0; //取消占比
            $item['qux_count2']      = !empty($qux_count2) ? count($qux_count2) : 0; //取消占比
            $item['qux_count3']      = !empty($qux_count3) ? count($qux_count3) : 0; //取消占比
            $item['qux_count4']      = !empty($qux_count4) ? count($qux_count4) : 0; //取消占比
            $item['qux_count5']      = !empty($qux_count5) ? count($qux_count5) : 0; //取消占比
            $item['qux_count6']      = !empty($qux_count6) ? count($qux_count6) : 0; //取消占比
            $item['qux_count7']      = !empty($qux_count7) ? count($qux_count7) : 0; //取消占比
            $item['qux_count8']      = !empty($qux_count8) ? count($qux_count8) : 0; //取消占比
            $item['qux_count9']      = !empty($qux_count9) ? count($qux_count9) : 0; //取消占比
            $item['qux_count10']     = !empty($qux_count10) ? count($qux_count10) : 0; //取消占比
            $order_ids               = array_column($order_id, 'order_id');
            $item['customerService'] = db('through')->where('role', 1)->whereIn('order_ids', $order_ids)->count();
            $item['shopowner']       = db('through')->where('role', 2)->whereIn('order_ids', $order_ids)->count();
            $item['order']           = db('order')->where(['channel_id' => $item['id'], 'city_id' => $item['city_id']])->count();
            
            return $item;
            
        });
        
        $p               = db('chanel')->where('type', 1)->select();
        $channel_details = db('channel_details')->where('type', 1)->select();
        return $this->fetch('xiao', ['list' => $list, 'p' => $p, 'channel_details' => $channel_details]);
    }
    
    
    private function t($res)
    {
        return db('goods_category')->where(['id' => $res, 'pro_type' => 1])->value('title');
    }
    
    public function Sec2Time($time)
    {
        if (is_numeric($time)) {
            $value = ["days" => 0, "hours" => 0, "minutes" => 0, "seconds" => 0,];
            
            if ($time >= 86400) {
                $value["days"] = floor($time / 86400);
                $time          = ($time % 86400);
            }
            if ($time >= 3600) {
                $value["hours"] = floor($time / 3600);
                $time           = ($time % 3600);
            }
            if ($time >= 60) {
                $value["minutes"] = floor($time / 60);
                $time             = ($time % 60);
            }
            $value["seconds"] = floor($time);
            //return (array) $value;
            $t = $value["days"] . "天" . " " . $value["hours"] . "小时" . $value["minutes"] . "分" . $value["seconds"] . "秒";
            return $t;
        } else {
            return (bool)false;
        }
    }
    
    /**
     * 话术模版下载
     */
    public function download()
    {
        
        $data     = Request::instance()->get();
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $xlsCell  = ['ID', '渠道一级', '渠道二级', '订单号', '一级类别', '二级类别', '联系人', '联系方式', '地址', '备注', '订单状态', '取消原因', '无效原因', '下单时间', '预约上门时间', '上门时间','精装房','是否有效', '总价', '接单人', '店铺', '打分', '满意度', '内容', '不满意', '最好的地方', '核算成本', '推荐人', '小区名称', '所属物业'];
        $xlsData  = db('order')
            ->alias('a')
            ->field('a.*,b.title,us.username as namekl,us.ce,st.store_name,co.type,co.content,cy.residential_id,cy.management,group.username as  content1,goo.title as problemTitle, goo1.title as problemTitle1,concat(p.province,c.city,u.county,a.addres) as  address,channel_details.title as channel_detailsTitle,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->join('chanel b', 'a.channel_id=b.id', 'left')
            ->join('channel_details', 'a.channel_details=channel_details.id', 'left')
            ->join('goods_category goo', 'a.pro_id=goo.id', 'left')
            ->join('goods_category goo1', 'a.pro_id1=goo1.id', 'left')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('personal personal', 'a.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('store st', 'st.store_id=us.store_id', 'left')
            ->join('community cy', 'a.order_id=cy.id', 'left')
            ->join('group_community group', 'a.group_community=group.group_id', 'left')
            ->join('cost co', 'co.order_id=a.order_id', 'left');
        if (isset($data['status']) && $data['status'] != '') {
            
            if ($data['status'] == 20) {
                $xlsData->where(['a.state' => 0]);
            } elseif ($data['status'] == 6) {
                $xlsData->where(['a.state' => 7, 'a.product_id' => ['<>', 0]]);
            } elseif ($data['status'] == 7) {
                $xlsData->where(['a.state' => $data['status'], 'a.product_id' => ['EQ', 0]]);
            } elseif ($data['status'] == 5) {
                $xlsData->where(['a.state' => ['between', [4, 5]]])->order('a.order_id desc');
            } else {
                $xlsData->where(['a.state' => $data['status']])->order('a.order_id desc');
            }
            if ($data['status'] != 0 && $data['status'] != 20) {
                $xlsData->where(['us.ce' => ['<>', 2]]);
            }
        }
        if (!empty($data['addres'])) {
            $xlsData->where(['a.addres' => ['like', "%{$data['addres']}%"]]);
            
        }
        
        if (!empty($data['city_id'])) {
            
            $xlsData->where(['a.city_id' => $data['city_id']]);
        }
        if (isset($data['management']) && $data['management'] != '') {
            
            $xlsData->where(['cy.management|personal.username' => ['like', "%{$data['management']}%"]]);
        }
        if ($node['rule'] != '*') {
            
            if (!empty($this->admin['store_id'])) {
                $xlsData->where(['us.store_id' => $this->admin['store_id']]);
            }
            if (!empty($this->admin['visit'])) {
                $xlsData->where(['a.created_time' => ['<',$this->admin['visit']]]);
            }
            if (!empty($this->admin['qu'])) {
                $xlsData->where(['a.channel_id' => ['in',$this->admin['qu']]]);
            }
            if (!empty($this->admin['residential_id'])) {
                $xlsData->whereIn('a.group_community|group_community.group_id', $this->admin['residential_id']);
            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')){
                    $xlsData->where('a.group_community','0000001');
                }
                
            }
        }
        
        if (isset($data['start_time']) && $data['start_time'] != '') {
            
            $xlsData->where(['a.created_time' => ['>=', strtotime($data['start_time'])]]);
        }
        if (isset($data['end_time']) && $data['end_time'] != '') {
            
            $xlsData->where(['a.created_time' => ['<=', strtotime($data['end_time']) + 24 * 3600]]);
        }
        
        if (isset($data['contacts']) && $data['contacts'] != '' && $data['chen'] != '' && $data['chen']) {
            
            $xlsData->where(['a.' . $data['chen'] . '' => ['like', "%{$data['contacts']}%"]]);
        };
        if (isset($data['channel_id']) && $data['channel_id'] != '') {
            
            $xlsData->where(['a.channel_id' => $data['channel_id']]);
            
        };
        if (isset($data['full']) && $data['full'] != '') {
            $product = db('product')->where(['full' => ['like', "%{$data['full']}%"]])->distinct(true)->column('orders_id');
            
            $xlsData->whereIn('a.order_id', $product);
            
        };
        if (isset($data['store_id']) && $data['store_id'] != '') {
            
            $xlsData->where(['us.store_id' => $data['store_id']]);
            
            
        };
        if (isset($data['assignor']) && $data['assignor'] != '') {
            $xlsData->where(['us.username' => ['like', "%{$data['assignor']}%"]]);
        };
        
        if (isset($data['remote_time']) && $data['remote_time'] != '') {
            $ytime  = strtotime(date("Y-m-d", strtotime($data['remote_time'])));//昨天开始时间戳
            $yetime = $ytime + 24 * 60 * 60 - 1;//昨天结束时间戳
            $title  = db('through')->where(['end_time' => ['between', [$ytime, $yetime]]])->field('order_ids')->select();
            $s      = '';
            foreach ($title as $key => $val) {
                $s .= $val['order_ids'] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');
            if ($data) {
                $xlsData->where(['a.order_id' => ['in', $data]]);
            }
            
        };
        if (isset($data['remote']) && $data['remote'] != '') {
            $title = db('through')->where(['status' => ['like', "%{$data['remote']}%"]])->field('order_ids')->order('through_id desc')->select();
            $s     = '';
            foreach ($title as $key => $val) {
                $s .= $val["order_ids"] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');
            
            if ($data) {
                $xlsData->where(['a.order_id' => ['in', $data]]);
            }
            
        };
        
        $ue = $xlsData->order('a.created_time desc')->select();;
        foreach ($ue as $k => $v) {
            $ues[$k]['order_id']        = $v['order_id'];
            $ues[$k]['channel_id']      = $v['title'];
            $ues[$k]['channel_details'] = $v['channel_detailsTitle'];
            $ues[$k]['order_no']        = $v['order_no'] . "\t";
            $ues[$k]['pro_id']          = $v['problemTitle'];
            $ues[$k]['pro_id1']         = $v['problemTitle1'];
            $ues[$k]['contacts']        = $v['contacts'];
            $ues[$k]['telephone']       = $v['telephone'] . "\t";
            $ues[$k]['addres']          = $v['address'];
            $ues[$k]['remarks']         = $v['remarks'];
            if ($v['state'] == 7 && $v['product_id'] == 0) {
                $o = "已完工";
            } elseif ($v['state'] == 7 && $v['product_id'] != 0) {
                $o = "已完成";
            } else {
                $o = OrderModel::type($v['state']);
            }
            $ues[$k]['states'] = $o;
            $ues[$k]['reason'] = $v['reason'];
            $ues[$k]['wu']     = $v['state'] != 10 ? '' : Db::connect(config('database.cc'))->table('invalid')->where('order_id', $v['order_id'])->field('concat(reason,"-",notereason) as reasons')->find()['reasons'];;
            
            $ues[$k]['created_time'] = date('Y-m-d H:i:s', $v['created_time']) . "\t";
            $punch_clock=db('clock_in')->where('order_id', $v['order_id'])->order('order_id desc')->find();
            
            if ($v['planned']) {
                $ues[$k]['planned'] = date('Y-m-d H:i:s', $v['planned']) . "\t";
            } else {
                $ues[$k]['planned'] = '';
            }
            $ues[$k]['punch_clock'] =empty($punch_clock)?'': date('Y-m-d H:i:s', $punch_clock['punch_clock']) . "\t";
            
            $ues[$k]['hardbound'] =empty($v['hardbound'])?'不是':'是';
            if ($v['state'] != 10) {
                $ues[$k]['you'] = "有效";
            } else {
                $ues[$k]['you'] = "无效";
            }
            if ($v['state'] > 3 && $v['state'] != 10) {
                $turnover0 = (new OrderModel())->newOffer($v['order_id']);
                $amount    = $turnover0['amount'];
            } else {
                $amount = 0;
            }
            $ues[$k]['turnover']   = $amount;
            $ues[$k]['namekl']     = $v['namekl'];
            $ues[$k]['store_name'] = $v['store_name'];
            
            $product                 = db('product')->where(['orders_id' => $v['order_id']])->field('score,full,content as content3,dissatisfied,best')->order('product_id desc')->find();
            $ues[$k]['score']        = $product['score'];
            $ues[$k]['full']         = $product['full'];
            $ues[$k]['content3']     = $product['content3'];
            $ues[$k]['dissatisfied'] = $product['dissatisfied'];
            $ues[$k]['best']         = $product['best'];
            if ($v['type'] == 1) {
                $ues[$k]['type'] = '是';
            } elseif ($v['type'] == 5) {
                $ues[$k]['type'] = '否';
            } else {
                $ues[$k]['type'] = '待定';
            }
            $ues[$k]['content'] = $v['content'];
            
            if ($v['channel_id'] == 35 && ($v['channel_details'] == 118 || $v['channel_details'] == 120|| $v['channel_details'] == 119) && $v['tui_jian'] != 0) {
                $ues[$k]['management']           = $v['username2'];
                $ues[$k]['content1']             = $v['usernames'];
                $ues[$k]['residential_quarters'] = $v['usernames1'];
            } else {
                $ues[$k]['management']           = $v['management'];
                $ues[$k]['residential_quarters'] = $v['residential_quarters'];
                $ues[$k]['content1']             = $v['content1'];
            }
            
            $through = db('through')->where(['order_ids' => $v['order_id']])->order('th_time desc')->limit(0, 3)->select();
            foreach ($through as $l => $value) {
                if ($value['role'] == 1) {
                    $ues[$k]['roles' . $l] = '客服';
                } elseif ($value['role'] == 2) {
                    $ues[$k]['roles' . $l] = '店长';
                } else {
                    $ues[$k]['roles' . $l] = '师傅';
                }
                if ($value['role'] == 1) {
                    $ues[$k]['username' . $l] = db('admin')->where('admin_id', $value['admin_id'])->value('username');
                } elseif ($value['role'] == 2) {
                    $ues[$k]['username' . $l] = db('user')->where('user_id', $value['admin_id'])->value('username');
                } else {
                    $ues[$k]['username' . $l] = Db::connect(config('database.db2'))->table('app_user')->where('id', $value['gid'])->value('username');
                }
                $ues[$k]['remar' . $l]   = $value['remar'];
                $ues[$k]['th_time' . $l] = date('Y-m-d H:i:s', $value['th_time']) . "\t";;
                
            }
            unset($ue[$k]);
            
            
        }
        
        $user = $this->check_authority();
        $user = ['admin_id' => $user['admin_id'], 'created_at' => time(), 'content' => '下载了订单记录', 'ord_id' => '',
        
        ];
        
        Cache::rm('status');
        Cache::rm('startTime');
        Cache::rm('endTime');
        Cache::rm('contacts');
        Cache::rm('channelId');
        Cache::rm('storeId');
        Cache::rm('assignor');
        Cache::rm('chen');
        Cache::rm('full');
        Cache::rm('remoteTime');
        Cache::rm('remote');
        Cache::rm('addres');
        Cache::rm('cityId');
        if(!empty($ues)){
            db('log')->insertGetId($user);
            vendor('phpcvs.Csv');
            $csv = new Csv();
            $po  = [];
            for ($i = 0; $i < 3; $i++) {
                $po[] = '分类';
                $po[] = '姓名';
                $po[] = '备注';
                $po[] = '跟进时间';
            }
            $xlsCell = array_merge($xlsCell, $po);
            $csv->export($ues, $xlsCell);
            return res_date($ues);
        }
        return '暂无数据';
        
        
        
    }
    
    
    //签单订单数据
    public function order_list()
    {
        $param    = Request::instance()->get();
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $list     = db('order')
            ->join('contract', 'contract.orders_id = order.order_id', 'left')
            ->join('chanel', 'order.channel_id=chanel.id', 'left')
            ->join('channel_details', 'order.channel_details=channel_details.id', 'left')
            ->join('user', 'user.user_id = order.assignor', 'left')
            ->join('goods_category', 'goods_category.id = order.pro_id', 'left')
            ->join('goods_category goods_category1', 'goods_category1.id = order.pro_id1', 'left')
            ->join('community', 'community.id = order.order_id', 'left')
            ->join('city', 'city.city_id = order.city_id', 'left')
            ->join('county', 'county.county_id = order.county_id', 'left')
            ->join('product', 'product.product_id = order.product_id', 'left')
            ->join('personal personal', 'order.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('store', 'store.store_id=user.store_id', 'left')
            ->join('group_community group', 'order.group_community=group.group_id', 'left')
            ->join('complaint', 'complaint.order_id = order.order_id', 'left');
        if (!empty($param['order_store']) && !empty($param['order_store'])) {
            $list->where('user.store_id', intval($param['order_store']));
            
        }
        
        if (!empty($param['channel_id']) && !empty($param['channel_id'])) {
            $list->where('order.channel_id', intval($param['channel_id']));
            
        }
        if (!empty($param['city_id']) && !empty($param['city_id'])) {
            $list->where('order.city_id', intval($param['city_id']));
            
        }
        if (!empty($param['channel_details']) && !empty($param['channel_details'])) {
            $list->where('order.channel_details', intval($param['channel_details']));
            
        }
        if (!empty($param['assignor']) && !empty($param['assignor'])) {
            $list->where(['user.username' => ['like', "%{$param['assignor']}%"]]);
            
        }
        if (!empty($param['residential_id']) && !empty($param['residential_id'])) {
            $list->where(['group.username|group_community.username' => ['like', "%{$param['residential_id']}%"]]);
            
        }
        //时间区间
        if (!empty($param['start_time']) && !empty($param['end_time'])) {
            $start                   = strtotime($param['start_time'] . ' 00:00:00');
            $end                     = strtotime($param['end_time'] . ' 23:59:59');
            $param['order_timeType'] = isset($param['order_timeType']) ? $param['order_timeType'] : 2;
            switch (intval($param['order_timeType'])) {
                case 1://录入时间
                    $list->whereBetween('order.created_time', [$start, $end]);
                    break;
                case 2://签约时间
                    $list->whereBetween('contract.con_time', [$start, $end]);
                    $list->whereBetween('order.state', [4, 7]);
                    break;
                case 3://开工时间
                    $list->whereBetween('order.start_time', [$start, $end]);
                    break;
                case 4://完工时间
                    $list->whereBetween('order.finish_time', [$start, $end]);
                    break;
                case 5://完款时间
                    $list->whereBetween('order.received_time', [$start, $end]);
                    break;
                case 6://结算时间
                    $list->whereBetween('order.cleared_time', [$start, $end]);
                    break;
                
            }
        } else {
            $list->whereBetween('order.state', [4, 7]);
        }
        
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $list->where(['store.store_id' => $this->admin['store_id']]);
            }
            if (!empty($this->admin['qu'])) {
                $list->whereIn('order.channel_id', $this->admin['qu']);
            }
            if (!empty($this->admin['residential_id'])) {
                
                $list->where(function ($query) {
                    $query->whereIn('order.group_community', $this->admin['residential_id'])->whereOr(['group_community.group_id' => ['in', $this->admin['personal_id']]]);
                });
                
                
            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')) {
                    $list->where('order.group_community', '0000001');
                }
            }
        }
        
        $list->where('user.ce', 'neq', 2)->field('order.*,goods_category.title as category_title,goods_category1.title as category_title1,city.city,county.county,product.product_id as product,complaint.order_id as complaint,community.residential_id,community.management,user.username,user.mobile,user.store_id,contract.con_time,channel_details.title as channel_details_title,chanel.title as chanel_title,store.store_name,group.username as residential_names,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1');
        if (isset($param['order_timeType']) && $param['order_timeType'] == 2) {
            $list->order('contract.con_time desc');
        } else {
            $list->order('order.order_id desc');
        }
        
        $data = $list->paginate(10)->each(function ($v, $k) {
            //计算成交额
            $turnover = (new OrderModel())->newOffer($v['order_id'])['amount'];
            //计算收款额度
            $payment = \app\api\model\Common::order_for_payment($v['order_id']);
            $payment = $payment[0] + $payment[1];
            //            //计算增项金额
            //            $increment=db('capital')
            //                ->where('ordesr_id', $v['order_id'])
            //                ->where('types', 1)
            //                ->where('enable', 1)
            //                ->where('increment', 1)
            //                ->sum('to_price');
            
            $v['user_name']        = $v['username'];
            $v['user_mobile']      = $v['mobile'];
            $v['store']            = $v['store_name'];
            $v['residential_name'] = $v['residential_names'];
            $v['wechat']           = intval($v['wechat']) == 1 ? '已加' : '未加';
            
            $v['notcost_time'] = intval($v['notcost_time']) > 0 ? '不计入' : '计入' . date('Y年m月', $v['created_time']);
            $v['state']        = OrderModel::type($v['state']);
            if ($turnover > 0 && $turnover == $payment) {
                $v['payment_state'] = '已完款';
            } else {
                $v['payment_state'] = '未完款';
            }
            if (intval($v['cleared_time']) > 0) {
                $v['cleared_state'] = '已结算';
            } else {
                $v['cleared_state'] = '未结算';
            }
            $v['address'] = $v['city'] . $v['county'] . $v['addres'];
            $v['mobile']  = $v['telephone'];
            
            
            $v['order_question']  = $v['category_title'];
            $v['created_time']    = intval($v['created_time']) > 0 ? date('Y-m-d', $v['created_time']) : '';
            $v['signing_time']    = intval($v['con_time']) > 0 ? date('Y-m-d', $v['con_time']) : '';
            $v['start_time']      = intval($v['start_time']) > 0 ? date('Y-m-d', $v['start_time']) : '';
            $v['finish_time']     = intval($v['finish_time']) > 0 ? date('Y-m-d', $v['finish_time']) : '';
            $v['received_time']   = intval($v['received_time']) > 0 ? date('Y-m-d', $v['received_time']) : '';
            $v['cleared_time']    = intval($v['cleared_time']) > 0 ? date('Y-m-d', $v['cleared_time']) : '';
            $v['acceptance_time'] = intval($v['acceptance_time']) > 0 ? date('Y-m-d', $v['acceptance_time']) : '';
            $v['payment_after']   = intval($v['payment_after']) > 0 ? date('Y-m-d', $v['payment_after']) : '';
            $v['settlement_time'] = intval($v['settlement_time']) > 0 ? date('Y-m-d', $v['settlement_time']) : '';
            
            //人工费
            //实际人工
            //            $artificial=order_for_reality_artificial([['order_id'=>$v['order_id']]]);
            //实际材料
            //            $material=order_for_reality_material([['order_id'=>$v['order_id']]]);
            //费用报销
            //            $outlay=order_for_outlay([['order_id'=>$v['order_id']]]);
            //利润
            //            $lirun                    =$turnover - $artificial - $material - $outlay;
            $data[$k]['payment_ratio'] = 0;
            $data[$k]['lirun_ratio']   = 0;
            //            if ($turnover > 0) {
            //                //完款比例
            //                $data[$k]['payment_ratio']=round(($payment / $turnover) * 100, 2);
            //                //利润率
            //                $data[$k]['lirun_ratio']=round(($lirun / $turnover) * 100, 2);
            //            }
            if ($v['channel_id'] == 35 && ($v['channel_details'] == 118 || $v['channel_details'] == 120|| $v['channel_details'] == 119) && $v['tui_jian'] != 0) {
                $v['management']           = $v['username2'];
                $v['residential_name']     = $v['usernames'];
                $v['residential_quarters'] = $v['usernames1'];
            }
            $v['turnover'] = $turnover;
            $v['payment']  = $payment;
            //            $v['increment']    =$increment;
            //            $v['payment_ratio']=$data[$k]['payment_ratio'] . '%';
            //            $v['artificial']   =$artificial;
            //            $v['material']     =$material;
            //            $v['outlay']       =$outlay;
            //            $v['lirun']        =$lirun;
            //            $v['lirun_ratio']  =$data[$k]['lirun_ratio'];
            
            return $v;
        });
        if ($this->admin['qu']) {
            $p = db('chanel')->where('type', 1)->whereIn('id', $this->admin['qu'])->select();
        } else {
            $p = db('chanel')->where('type', 1)->select();
        }
        $channelPid = array_column($p, 'id');
        $city       = db('city')->where("city_id", "in", "241,239")->select();
        
        if ($node['rule'] != '*') {
            $store = db('store');
            if (!empty($this->admin['store_id'])) {
                $store->where(['store_id' => $this->admin['store_id']]);
            }
            $store = $store->select();
        } else {
            $store = db('store')->select();
        }
        $channel_details = db('channel_details')->where('type', 1)->whereIn('pid', $channelPid)->select();
        if ($node['rule'] != '*') {
            $residential = db('group_community')->whereIn('group_id', $this->admin['residential_id'])->select();
        } else {
            $residential = db('group_community')->where('pid',0)->select();
        }
        return $this->fetch('orderList', ['list' => $data, 'p' => $p, 'l' => $residential, 'channel_details' => $channel_details, 'store' => $store, 'node' => $node, 'hide' => $this->admin['hide'], 'admin' => $this->admin, 'city' => $city, 'data' => $param]);
        
    }
    
    public function orderOriginal()
    {
        $data     = Request::instance()->get();
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $model    = db('order')->alias('a')
            ->field('a.*,b.title,us.username as namekl,us.ce,st.store_name,cy.residential_id,cy.management,group.username  as content1,goo.title as problemTitle,goo1.title as problemTitle1,goo2.title as problemTitle2, concat(p.province,c.city,u.county,a.addres) as  address,channel_details.title as channel_detailsTitle,c.city,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->join('chanel b', 'a.channel_id=b.id', 'left')
            ->join('channel_details', 'a.channel_details=channel_details.id', 'left')
            ->join('goods_category goo', 'a.pro_id=goo.id', 'left')
            ->join('goods_category goo1', 'a.pro_id1=goo1.id', 'left')
            ->join('goods_category goo2', 'a.pro_id2=goo2.id', 'left')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('personal personal', 'a.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('store st', 'st.store_id=us.store_id', 'left')
            ->join('community cy', 'a.order_id=cy.id', 'left')
            ->join('group_community group', 'order.group_community=group.group_id', 'left');
        if (isset($data['status']) && $data['status'] != '') {
            if ($data['status'] == 20) {
                $model->where(['a.state' => 0]);
            } elseif ($data['status'] == 6) {
                $model->where(['a.state' => 7, 'a.product_id' => ['<>', 0]]);
            } elseif ($data['status'] == 7) {
                $model->where(['a.state' => $data['status'], 'a.product_id' => ['EQ', 0]]);
            } elseif ($data['status'] == 5) {
                $model->where(['a.state' => ['between', [4, 5]]])->order('a.order_id desc');
            } else {
                $model->where(['a.state' => $data['status']])->order('a.order_id desc');
            }
            //            if ($data['status'] != 0 && $data['status'] != 20) {
            //                $model->where(['us.ce'=>['<>', 2]]);
            //            }
        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $model->where(['group.username|group_community.username' => ['like', "%{$data['residential_id']}%"]]);
            
        }
        if (!empty($data['addres'])) {
            if ($data['status'] == 20) {
                $model->where(['a.addres' => ['like', "%{$data['addres']}%"]]);
                
            }
        }
        if (!empty($data['city_id'])) {
            
            $model->where(['a.city_id' => $data['city_id']]);
        }
        if (isset($data['management']) && $data['management'] != '') {
            
            $model->where(['cy.management|personal.username' => ['like', "%{$data['management']}%"]]);
        }
        
        if (isset($data['start_time']) && $data['start_time'] != '' && isset($data['end_time']) && $data['end_time'] != '') {
            $model->whereBetween('a.created_time', [strtotime($data['start_time']), strtotime($data['end_time']) + 24 * 3600]);
        }
        
        if (isset($data['contacts']) && $data['contacts'] != '' && $data['chen'] != '' && $data['chen']) {
            $model->where(['a.' . $data['chen'] . '' => ['like', "%{$data['contacts']}%"]]);
        };
        
        if (isset($data['full']) && $data['full'] != '') {
            
            $product = db('product')->where(['full' => ['like', "%{$data['full']}%"]])->distinct(true)->column('orders_id');
            $model->whereIn('a.order_id', $product);
        };
        if (isset($data['store_id']) && $data['store_id'] != '') {
            
            $model->where(['us.store_id' => $data['store_id']]);
        };
        if (isset($data['assignor']) && $data['assignor'] != '') {
            
            $model->where(['us.username' => ['like', "%{$data['assignor']}%"]]);
        };
        if (isset($data['remote_time']) && $data['remote_time'] != '') {
            
            $ytime  = strtotime(date("Y-m-d", strtotime($data['remote_time'])));//昨天开始时间戳
            $yetime = $ytime + 24 * 60 * 60 - 1;//昨天结束时间戳
            $title  = db('through')->where(['end_time' => ['between', [$ytime, $yetime]]])->distinct(true)->column('order_ids');
            if ($title) {
                $model->whereIn('a.order_id', $title);
            }
        };
        if (isset($data['remote']) && $data['remote'] != '') {
            
            $title = db('through')->where(['status' => ['like', "%{$data['remote']}%"]])->distinct(true)->order('through_id desc')->column('order_ids');
            if (!empty($title)) {
                $model->whereIn('a.order_id', $title);
            }
            
        };
        
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $model->where(['st.store_id' => $this->admin['store_id']]);
            }
            
            if (!empty($this->admin['qu'])) {
                
                $model->where(['a.channel_id' => ['in', $this->admin['qu']]]);
            }
            
            
            if (!empty($this->admin['residential_id'])) {
                
                $model->where(function ($query) {
                    $query->whereIn('order.group_community|group_community.group_id', $this->admin['residential_id']);
                });
                
                
            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')) {
                    $model->where('order.group_community', '0000001');
                }
            }
            
        }
        if (isset($data['channel_id']) && $data['channel_id'] != '') {
            
            $model->where(['a.channel_id' => $data['channel_id']]);
        };
//                $ues=$model->order('a.created_time desc')->select();echo $model->getLastsql();die;
        $ues = $model->order('a.created_time desc')->paginate(10)->each(function ($v, $k) {
            if ($v['state'] == 7 && $v['product_id'] == 0) {
                $o = "已完工";
            } elseif ($v['state'] == 7 && $v['product_id'] != 0) {
                $o = "已完成";
            } else {
                $o = OrderModel::type($v['state']);
            }
            $v['states']       = $o;
            $v['created_time'] = date('Y-m-d', $v['created_time']);
            $v['planned']      = empty($v['planned']) ? '' : date('Y-m-d', $v['planned']);
            if ($v['state'] != 10) {
                $v['you'] = "有效";
            } else {
                $v['you'] = "无效";
            }
            
            if ($v['state'] > 3) {
                $turnover0 = (new OrderModel())->newOffer($v['order_id']);
                $amount    = $turnover0['amount'];
            } else {
                $amount = 0;
            }
            $v['turnover'] = $amount;
            if ($v['state'] = 7) {
                $product = db('product')->where(['orders_id' => $v['order_id']])->field('score,full,content as content3,dissatisfied,best')->order('product_id desc')->find();
                
            }
            $v['score']        = empty($product['score']) ? '' : $product['score'];
            $v['full']         = empty($product['full']) ? '' : $product['full'];
            $v['content3']     = empty($product['content3']) ? '' : $product['content3'];
            $v['dissatisfied'] = empty($product['dissatisfied']) ? '' : $product['dissatisfied'];
            $v['best']         = empty($product['best']) ? '' : $product['best'];
            
            $through = db('through')->where(['order_ids' => $v['order_id']])->order('th_time desc')->limit(0, 3)->select();
            foreach ($through as $l => $value) {
                if ($value['role'] == 1) {
                    $through[$l]['roles'] = '客服';
                } elseif ($value['role'] == 2) {
                    $through[$l]['roles'] = '店长';
                } else {
                    $through[$l]['roles'] = '师傅';
                }
                if ($value['role'] == 1) {
                    $through[$l]['username'] = db('admin')->where('admin_id', $value['admin_id'])->value('username');
                } elseif ($value['role'] == 2) {
                    $through[$l]['username'] = db('user')->where('user_id', $value['admin_id'])->value('username');
                } else {
                    $through[$l]['username' . $l] = Db::connect(config('database.db2'))->table('app_user')->where('id', $value['gid'])->value('username');
                }
                $through[$l]['remar']   = $value['remar'];
                $through[$l]['th_time'] = date('Y-m-d', $value['th_time']);
                
            }
            if ($v['channel_id'] == 35 && ($v['channel_details'] == 118 || $v['channel_details'] == 120|| $v['channel_details'] == 119) && $v['tui_jian'] != 0) {
                $v['management']           = $v['username2'];
                $v['content1']             = $v['usernames'];
                $v['residential_quarters'] = $v['usernames1'];
            }
            $v['through'] = empty($through) ? [] : $through;
            
            $subhead=db('order_problem')->where(['order_id' => $v['order_id']])->where('goods_category_id',$v['pro_id'])->column('subhead');
            
            $v['subhead'] = implode($subhead,',');
            return $v;
            
        });
        
        $po = [];
        for ($i = 0; $i < 2; $i++) {
            $po[$i]['title']  = '分类';
            $po[$i]['name']   = '姓名';
            $po[$i]['remake'] = '备注';
            $po[$i]['time']   = '跟进时间';
        }
        
        if ($this->admin['qu']) {
            $p = db('chanel')->where('type', 1)->whereIn('id', $this->admin['qu'])->select();
        } else {
            $p = db('chanel')->where('type', 1)->select();
        }
        if ($this->admin['store_id']) {
            $l = db('store')->where('store_id', $this->admin['store_id'])->select();
        } else {
            $l = db('store')->select();
        }
        
        $r    = db('remote')->where(['is_enable' => 1, 'parent_id' => 0])->select();
        $city = db('city')->where("city_id", "in", "241,239")->select();
        if ($node['rule'] != '*') {
            $residential = db('group_community')->whereIn('group_id', $this->admin['residential_id'])->select();
        } else {
            $residential = db('group_community')->where('pid',0)->select();
        }
        return $this->fetch('orderOriginal', ['list' => $ues, 'ls' => $residential, 'lists' => $po, 'p' => $p, 'l' => $l, 'r' => $r, 'city' => $city, 'node' => $node, 'hide' => $this->admin['hide'], 'admin' => $this->admin, 'data' => $data]);
        
        
    }
    
    /*
     * 社区关联情况分析
     */
    public function associatedCommunities()
    {
        $data     = Request::instance()->get();
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $mp       = db('order')->join('city', 'order.city_id=city.city_id', 'left')->join('goods_category', 'order.pro_id=goods_category.id', 'left')->join('community cy', 'order.order_id=cy.id', 'left')->join('personal personal', 'order.tui_jian=personal.personal_id', 'left')->join('personal personals', 'personal.num=personals.personal_id', 'left')->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')->join('user us', 'order.assignor=us.user_id', 'left')->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')->join('residential', 'residential.residential_id=cy.residential_id', 'left')->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,city.city,order.city_id,goods_category.title,residential.content,order.residential_quarters,order.pro_id,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),order.city_id,cy.residential_id,order.residential_quarters,order.pro_id");
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start = strtotime($data['start_time'] . ' 00:00:00');
            $end   = strtotime($data['end_time'] . ' 23:59:59');
            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $mp->where(['residential.content|group_community.username' => ['like', "%{$data['residential_id']}%"]]);
        }
        if (!empty($data['residential_quarters']) && !empty($data['residential_quarters'])) {
            $mp->where('order.residential_quarters', $data['residential_quarters']);
        }
        if (!empty($data['city_id']) && !empty($data['city_id'])) {
            $mp->where('order.city_id', intval($data['city_id']));
        }
        if (!empty($data['pro_id']) && !empty($data['pro_id'])) {
            $mp->where('order.pro_id', intval($data['pro_id']));
        }
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $mp->where(['us.store_id' => $this->admin['store_id']]);
            }
            
            if (!empty($this->admin['qu'])) {
                $mp->whereIn('order.channel_id', $this->admin['qu']);
            }
            
            if (!empty($this->admin['residential_id'])) {
                $mp->whereIn('cy.residential_id', $this->admin['residential_id']);
            }
            
        }
        $list           = $mp->order('posttime desc')->paginate(15)->each(function ($item, $key) {
            $OrderQuantity = [];
            $deal          = [];
            $todayStart    = strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd      = strtotime($item['posttime'] . ' 23:59:59');
            //线索量
            $order = db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id' => $item['city_id'], 'residential_quarters' => $item['residential_quarters'], 'pro_id' => $item['pro_id']])->field('city_id,channel_id,planned,state')->select();
            
            foreach ($order as $value) {
                //订单量
                if ($value['state'] != 10) {
                    $OrderQuantity[] = $value;
                } //上门量
                if (!empty($value['planned'])) {
                    $deal[] = $value;
                }
            }
            $item['order']          = count($order);
            $item['OrderQuantity']  = count($OrderQuantity);
            $item['Efficiency']     = sprintf("%.2f", count($OrderQuantity) / count($order));
            $item['dealCount']      = count($deal);
            $item['doorToDoorRate'] = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", count($deal) / count($OrderQuantity));
            $signUp                 = db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.residential_quarters' => $item['residential_quarters'], 'order.pro_id' => $item['pro_id']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            $item['signUp']         = count($signUp);
            
            $item['doorToDoorTransactionRate'] = empty($deal) ? 0 : sprintf("%.2f", $item['signUp'] / count($deal));
            $item['orderTurnoverRate']         = empty(count($OrderQuantity)) ? 0 : sprintf("%.2f", $item['signUp'] / count($OrderQuantity));
            if (!empty($signUp)) {
                $amount = 0;
                foreach ($signUp as $o) {
                    $turnover0 = (new OrderModel())->newOffer($o);
                    $amount    += $turnover0['amount'];
                }
            } else {
                $amount = 0;
            }
            $item['turnover']  = $amount;
            $item['turnoverS'] = empty($item['signUp']) ? 0 : sprintf("%.2f", $amount / $item['signUp']);
            return $item;
        });
        $city           = db('city')->where("city_id", "in", "241,239")->select();
        $residential    = db('residential')->select();
        $goods_category = db('goods_category')->where('parent_id', 0)->select();
        return $this->fetch('associatedCommunities', ['list' => $list, 'city' => $city, 'l' => $residential, 'goods_category' => $goods_category, 'residential_quarters' => self::residential_quarters(), 'data' => $data]);
    }
    
    /*
     * 城市店长接单转化数据情况分析
     */
    public function cityStoreManagerReceivesOrders()
    {
        
        $data     = Request::instance()->get();
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $mp       = db('order')->join('city', 'order.city_id=city.city_id', 'left')->join('community cy', 'order.order_id=cy.id', 'left')->join('user', 'order.assignor=user.user_id', 'left')->join('store', 'user.store_id=store.store_id', 'left');
        
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start = strtotime($data['start_time'] . ' 00:00:00');
            $end   = strtotime($data['end_time'] . ' 23:59:59');
            
            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        if (!empty($data['city_id']) && !empty($data['city_id'])) {
            
            $mp->where('order.city_id', $data['city_id']);
        }
        if (!empty($data['user_id']) && !empty($data['user_id'])) {
            
            $mp->where('order.assignor', $data['user_id']);
        }
        if (!empty($data['store_id']) && !empty($data['store_id'])) {
            
            $mp->where('user.store_id', $data['store_id']);
        }
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $mp->where(['us.store_id' => $this->admin['store_id']]);
            }
            
            if (!empty($this->admin['qu'])) {
                $mp->whereIn('order.channel_id', $this->admin['qu']);
            }
            
            if (!empty($this->admin['residential_id'])) {
                $mp->whereIn('cy.residential_id', $this->admin['residential_id']);
            }
            
        }
        $list = $mp->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,city.city,order.assignor,order.city_id,store.store_name,user.username')->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),city.city,order.assignor")->where('order.state', 'neq', 10)->order('posttime desc')->paginate(15)->each(function ($item, $key) {
            $todayStart = strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd   = strtotime($item['posttime'] . ' 23:59:59');
            //签约总业绩
            $signUp = db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.assignor' => $item['assignor']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            if (!empty($signUp)) {
                $amount = 0;
                foreach ($signUp as $o) {
                    $turnover0 = (new OrderModel())->newOffer($o);
                    $amount    += $turnover0['amount'];
                }
            } else {
                $amount = 0;
            }
            //接单量
            $order = db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id' => $item['city_id'], 'order.assignor' => $item['assignor']])->count();
            //上门量
            $deal = db('order')->whereBetween('planned', [$todayStart, $todayEnd])->where(['city_id' => $item['city_id'], 'order.assignor' => $item['assignor']])->count();
            //远程沟通
            $through           = db('through')->where(['admin_id' => $item['assignor']])->whereBetween('th_time', [$todayStart, $todayEnd])->count();
            $item['order']     = $order;
            $item['dealCount'] = $deal;
            
            $item['doorToDoorRate']            = empty($order) ? 0 : sprintf("%.2f", $deal / $order);
            $item['through']                   = $through;
            $item['throughCount']              = sprintf("%.2f", $through / $order);
            $item['signUp']                    = count($signUp);
            $item['turnover']                  = $amount;
            $item['doorToDoorTransactionRate'] = empty($deal) ? 0 : sprintf("%.2f", count($signUp) / $deal);
            $item['orderTurnoverRate']         = empty($deal) ? 0 : sprintf("%.2f", count($signUp) / $order);
            $item['turnoverS']                 = empty($item['signUp']) ? 0 : sprintf("%.2f", $amount / $item['signUp']);
            
            return $item;
        });
        $city = db('city')->where("city_id", "in", "241,239")->select();
        if ($this->admin['store_id']) {
            $l = db('store')->where('store_id', $this->admin['store_id'])->select();
        } else {
            $l = db('store')->select();
        }
        if ($this->admin['store_id']) {
            $user = db('user')->where('store_id', $this->admin['store_id'])->select();
        } else {
            $user = db('user')->where('ce', 'neq', 2)->select();
        }
        
        return $this->fetch('cityStoreManagerReceivesOrders', ['list' => $list, 'city' => $city, 'l' => $l, 'user' => $user, 'data' => $data]);
    }
    
    /*
     * 管家结算数据统计
     */
    public function housekeeperSettlement()
    {
        $data = Request::instance()->get();
        $mp   = db('order')
            ->join('city', 'order.city_id=city.city_id', 'left')
            ->join('community cy', 'order.order_id=cy.id', 'left')
            ->join('residential', 'residential.residential_id=cy.residential_id', 'left')
            ->field('FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,city.city,order.city_id,residential.content,order.residential_quarters,cy.management,order.residential_quarters')->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),order.city_id,cy.management");
        if (!empty($data['created_time']) && !empty($data['created_time'])) {
            $start     = strtotime(date('Y-m-d 00:00:00', strtotime($data['start_time'])));
            $BeginDate = date('Y-m-01', strtotime($data['start_time']));
            $end       = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month")));
            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        if (!empty($data['city_id']) && !empty($data['city_id'])) {
            
            $mp->where('order.city_id', $data['city_id']);
        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $mp->where('residential.residential_id', intval($data['residential_id']));
        }
        if (!empty($data['residential_quarters']) && !empty($data['residential_quarters'])) {
            $mp->where('order.residential_quarters', $data['residential_quarters']);
        }
        $list        = $mp->where('cy.management', 'neq', '')->order('posttime desc')->paginate(15)->each(function ($item, $key) {
            $OrderQuantity  = [];
            $OrderQuantity1 = [];
            $deal           = [];
            $todayStart     = strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd       = strtotime($item['posttime'] . ' 23:59:59');
            //推荐订单量
            $order = db('order')->join('community cy', 'order.order_id=cy.id', 'left')->whereBetween('order.created_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.residential_quarters' => $item['residential_quarters'], 'cy.management' => $item['management']])->field('order.city_id,order.planned,order.state,order.pro_id,order.pro_id1')->select();
            
            //                $personal=db('order')->join('personal personal', 'order.tui_jian=personal.personal_id', 'left')
            //                    ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            //                    ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            //                    ->whereBetween('order.created_time', [$todayStart, $todayEnd])
            //                    ->where(['order.city_id'=>$item['city_id'], 'group_community1.username'=>$item['residential_quarters'], 'personals.username'=>$item['management']])->field('order.city_id,order.planned,order.state,order.pro_id,order.pro_id1')->select();
            //                $order=array_values(array_merge($order,$personal));
            foreach ($order as $value) {
                if (!empty($value['planned'])) {
                    $deal[] = $value;
                }
                if ($value['pro_id'] == 1 && $value['pro_id1'] == 2) {
                    $OrderQuantity[] = $value;
                }
                if ($value['pro_id'] == 42) {
                    $OrderQuantity1[] = $value;
                }
            }
            $item['order'] = count($order);
            
            $item['dealCount'] = count($deal);
            $signUp            = db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->join('community cy', 'order.order_id=cy.id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id' => $item['city_id'], 'order.residential_quarters' => $item['residential_quarters'], 'cy.management' => $item['management']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            $item['signUp']    = count($signUp);
            
            $item['doorToDoorTransactionRate'] = empty($deal) ? 0 : sprintf("%.2f", $item['signUp'] / count($deal));
            $item['orderTurnoverRate']         = empty(count($order)) ? 0 : sprintf("%.2f", $item['signUp'] / count($order));
            if (!empty($signUp)) {
                $amount = 0;
                foreach ($signUp as $o) {
                    $turnover0 = (new OrderModel())->newOffer($o);
                    $amount    += $turnover0['amount'];
                }
            } else {
                $amount = 0;
            }
            $item['turnover']       = $amount;
            $item['turnoverS']      = empty($item['signUp']) ? 0 : sprintf("%.2f", $amount / $item['signUp']);
            $item['OrderQuantity']  = count($OrderQuantity);
            $item['OrderQuantity1'] = count($OrderQuantity1);
            return $item;
        });
        $city        = db('city')->where("city_id", "in", "241,239")->select();
        $residential = db('residential')->select();
        return $this->fetch('HousekeeperSettlement', ['list' => $list, 'city' => $city, 'l' => $residential, 'residential_quarters' => self::residential_quarters()]);
    }
    
    public static function residential_quarters()
    {
        $residential_quarters = \db('order')->where('residential_quarters', 'neq', '')->column('residential_quarters');
        
        return $residential_quarters;
    }
    
    /**
     * 订单详情
     */
    public function info($order_id = 0)
    {
        if (!$order_id) {
            $this->error('请刷新后重试');
        }
        $admin          = $this->check_authority();
        $res            = db('order')->field('order.*,u.username,u.store_id,b.*,p.province,c.city,y.county,co.*,st.*,be.*,be.uptime as uptimes,ku.*,cy.residential_id,cy.management,group.username as content1,channel.title as channel_title,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->join('chanel b', 'order.channel_id=b.id', 'left')
            ->join('channel_details channel', 'order.channel_details=channel.id', 'left')
            ->join('user u', 'order.assignor=u.user_id ', 'left')
            ->join('province p', 'order.province_id=p.province_id', 'left')
            ->join('city c', 'order.city_id=c.city_id', 'left')
            ->join('county y', 'order.county_id=y.county_id', 'left')
            ->join('contract co', 'order.contract_id=co.contract_id', 'left')
            ->join('startup st', 'order.startup_id=st.startup_id', 'left')
            ->join('kup ku', 'order.kupj=ku.kup_id', 'left')
            ->join('before be', 'order.before_id=be.before_id', 'left')
            ->join('community cy', 'order.order_id=cy.id', 'left')
            ->join('personal', 'order.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('group_community group', 'order.group_community=group.group_id', 'left')
            ->where(['order_id' => $order_id])->find();
        $res['pro_id']  = !empty($this->t($res['pro_id'])) ? $this->t($res['pro_id']) : '暂无数据';
        $res['pro_id1'] = !empty($this->t($res['pro_id1'])) ? $this->t($res['pro_id1']) : '暂无数据';
        $res['pro_id2'] = !empty($this->t($res['pro_id2'])) ? $this->t($res['pro_id2']) : '暂无数据';
        
        $timediff = $res['up_time'] - $res['sta_time'];
        
        
        $res['shichang'] = $this->Sec2Time($timediff);
        
        $res['states'] = OrderModel::type($res['state']);
        
        if (!$res) {
            $this->error('未找到该需求');
        }
        
        if ($res['logo']) {
            $res['logo'] = unserialize($res['logo']);
        }
        
        if ($res['tupian']) {
            $res['tupian'] = unserialize($res['tupian']);
        }
        if ($res['logos']) {
            $res['logos'] = unserialize($res['logos']);
        }
        
        if ($res['contract']) {
            $res['contract'] = unserialize($res['contract']);
        }
        
        if ($res['deposit']) {
            $res['deposit'] = unserialize($res['deposit']);
        }
        if ($res['invoice']) {
            $res['invoice'] = unserialize($res['invoice']);
        }
        if ($res['handover']) {
            $res['handover'] = unserialize($res['handover']);
        }
        //师傅详情;
        $shi['ids']           = $res['xiu_id'];
        $shi['sort_store_id'] = $res['store_id'];
        if (!empty($shi['ids'])) {
            $results = send_post(UIP_SRC . "/support-v1/user/old-list?ids=" . $shi['ids'] . '&sort_store_id=' . $shi['sort_store_id'], [], 2);
            $results = json_decode($results, true);
            if ($results['code'] == 200) {
                $res['shi'] = object_array($results['data']);
            } else {
                $res['shi'] = '';
            }
        } else {
            $res['shi'] = '';
        }
        $thro = db('through')->where(['order_ids' => $res['order_id'], 'baocun' => 1])->order('th_time desc')->find();
        if ($res['ification'] == 1) {
            $capital = db('capital')->where(['ordesr_id' => $res['order_id'], 'types' => 1, 'enable' => 1])->select();
            if ($capital) {
                if ($capital[0]['capitalgo']) {
                    $capitalgo = unserialize($capital[0]['capitalgo']);
                } else {
                    $capitalgo = '';
                }
                $give_d = $capital[0]['give_a'] . "\r\n" . $capital[0]['give_b'];
                if ($give_d) {
                    $give_d = !empty($give_d) ? $give_d : '';
                } else {
                    $give_d = '';
                }
                $gong         = $capital[0]['gong'];
                $give_remarks = !empty($capital[0]['give_remarks']) ? $capital[0]['give_remarks'] : '';
                
            } else {
                $capitalgo    = '';
                $give_d       = '';
                $gong         = 0;
                $give_remarks = '';
            }
            $cap                 = array_sum(array_column($capital, 'to_price'));
            $c                   = array_sum(array_column($capital, 'give_money'));
            $expense             = 0;
            $purchasing_discount = 0;
            $purchasing_expense  = 0;
            
        } else {
            if ($thro) {
                $envelopes = db('envelopes')->where(['through_id' => $thro['through_id']])->find();
            } else {
                $envelopes = db('envelopes')->where(['ordesr_id' => $res['order_id'], 'through_id' => 0])->find();
            }
            $capital = db('capital')->where(['ordesr_id' => $res['order_id'], 'types' => 1, 'enable' => 1])->select();
            if ($envelopes['capitalgo']) {
                $capitalgo = unserialize($envelopes['capitalgo']);
            } else {
                $capitalgo = '';
            }
            $res['wen_a']        = $envelopes['wen_a'];
            $give_d              = $envelopes['give_b'];
            $c                   = $envelopes['give_money'];
            $gong                = $envelopes['gong'];
            $give_remarks        = $envelopes['give_remarks'];
            $purchasing_discount = $envelopes['purchasing_discount'];
            $purchasing_expense  = $envelopes['purchasing_expense'];
            $expense             = $envelopes['expense'];
            $cap                 = array_sum(array_column($capital, 'to_price'));
        }
        
        if ($thro['baocun'] == 1) {
            $res['totalprice'] = $thro['amount'] + $expense - $c;
            $res['ok']         = 2;
        } else {
            $res['totalprice'] = $cap + $expense - $res['discount'] - $res['artificial'] - $res['material'] - $c;;
            $res['ok'] = 1;
        }
        //遠程溝通
        
        $through        = db('through')->where(['order_ids' => $res['order_id'], 'baocun' => 0, 'capital_id' => ['eq', ' ']])->order('th_time desc')->paginate(50)->each(function ($item, $key) {
            if ($item['gid']) {
                //师傅详情;
                $shi['ids'] = $item['gid'];
                $results    = send_post(UIP_SRC . "/support-v1/user/old-list", $shi);
                $results    = json_decode($results, true);
                if ($results['code'] == 200) {
                    if (empty($results['data'])) {
                        $item['gid'] = '';
                    } else {
                        $item['gid'] = $results['data'][0]['username'];
                    }
                } else {
                    $item['gid'] = '';
                }
            }
            if ($item['role'] == 1) {
                $item['admin_id'] = db('admin')->where('admin_id', $item['admin_id'])->value('username');
            } elseif ($item['role'] == 2) {
                $item['admin_id'] = db('user')->where('user_id', $item['admin_id'])->value('username');
            }
            $item['log'] = !empty($item['log']) ? unserialize($item['log']) : '';
            
            return $item;
        });
        $through1       = db('through')->where('order_ids', $res['order_id'])->where(function ($query) {
            $query->whereOr(['capital_id' => ['neq', ' '], 'baocun' => 1]);
        })->order('th_time desc')->paginate(50)->each(function ($item, $key) {
            if ($item['gid']) {
                //师傅详情;
                $shi['user_id'] = $item['gid'];
                $results        = send_post(UIP_SRC . "/shopowner/User/searchUserId", $shi);
                $results        = json_decode($results);
                if ($results->code == 200) {
                    $item['gid'] = object_array($results->data);
                } else {
                    $item['gid'] = '';
                }
            }
            if ($item['role'] == 1) {
                $item['admin_id'] = db('admin')->where('admin_id', $item['admin_id'])->value('username');
            } elseif ($item['role'] == 2) {
                $item['admin_id'] = db('user')->where('user_id', $item['admin_id'])->value('username');
            }
            
            
            return $item;
            
        });
        $cash           = 0;
        $WeChat         = 0;
        $res['payment'] = db('payment')->where('orders_id', $res['order_id'])->select();
        $res['product'] = db('product')->where(['orders_id' => $res['order_id']])->select();;
        if ($res['payment']) {
            foreach ($res['payment'] as $k => $v) {
                //new  app\api\model\Approval()
                $Approval = new Approval();
                if ($v['weixin'] != 2) {
                    $res['payment'][$k]['payment']  = array_values($Approval->filter_by_value((new Order())->PaymentMethod(2), 'id', $v['weixin']))[0]['title'];
                    $res['payment'][$k]['payment1'] = 1;
                    $cash                           += $v['money'];
                } else if ($v['weixin'] == 2) {
                    if ($v['success'] == 2) {
                        $res['payment'][$k]['payment'] = '*微信*已收款';
                    } else {
                        $res['payment'][$k]['payment'] = '*微信*未收款';
                    }
                    $WeChat += $v['money'];
                    
                    $res['payment'][$k]['payment1'] = 1;
                }
                if ($v['logos']) {
                    $res['payment'][$k]['logos'] = unserialize($v['logos']);
                } else {
                    $res['payment'][$k]['logos'] = '';
                }
            }
        }
        
        $r            = array_sum([$res['dep'], $res['dep_money'], $res['tail']]);
        $r1           = array_sum([$WeChat, $cash]);
        $res['total'] = $r + $r1;
        //标签
        $label               = db('label')->where('label_id', 'in', $res['label_id'])->select();
        $cos                 = db('cost')->where('order_id', $res['order_id'])->join('admin ad', 'ad.admin_id=cost.admin_id', 'left')->field('cost.*,ad.username')->find();
        $res['customers_id'] = db('customers')->where('customers_id', $res['customers_id'])->value('username');
        $complaint           = db('complaint')->where(['order_id' => $res['order_id']])->find();
        if ($complaint) {
            $complaint['shopowner'] = empty($complaint['shopowner']) ? [] : db('user')->where('user_id', $complaint['shopowner'])->value('username');
            
            $complaint['admins'] = empty($complaint['admin_id']) ? '' : db('admin')->where('admin_id', $complaint['admin_id'])->value('username');
            
            if (!empty($complaint['masterworker'])) {
                $complaint['masterworker'] = empty($complaint['masterworker']) ? '' : Db::connect(config('database.db2'))->table('app_user')->whereIn('id', explode(',', $complaint['masterworker']))->select();
            } else {
                $list['masterworker'] = [];
            }
            $complaint['picture'] = empty($complaint['picture']) ? [] : unserialize($complaint['picture']);
        }
        if ($res['channel_id'] == 35 && ($res['channel_details'] == 121 || $res['channel_details'] == 120|| $res['channel_details'] == 119) && $res['tui_jian'] != 0) {
            $res['management']           = $res['username2'];
            $res['content1']     = $res['usernames'];
            
        }
        $p = db('sign')->where('order_id', $res['order_id'])->find();
        return $this->fetch('info', ['data' => $res, 'capital' => $capital, 'label' => $label, 'through' => $through, 'through1' => $through1, 'give_d' => $give_d, 'gong' => $gong, 'c' => $c, 'expense' => $expense, 'capitalgo' => $capitalgo, 'give_remarks' => $give_remarks, 'cos' => $cos, 'complaint' => $complaint, 'sign' => $p, 'hide' => $admin['hide'], 'purchasing_discount' => $purchasing_discount, 'purchasing_expense' => $purchasing_expense]);
    }
    
}
